//
//  CommonSQL.m
//  DouBanAgain
//
//  Created by Bang-guo on 15-6-9.
//  Copyright (c) 2015年 Bang-guo. All rights reserved.
//

#import "CommonSQL.h"

@implementation CommonSQL


static sqlite3 *db = nil;
//4.打开数据库
+(void)openDB {
    
    if (db != nil) {
        
        return;//如果数据库对象存在则不往下执行
        
    }
    //创建数据库文件路径
    NSArray *array = NSSearchPathForDirectoriesInDomains(NSDocumentDirectory, NSUserDomainMask, YES);
    NSString *filePath = [array lastObject];
    NSString *sqlitePath = [filePath stringByAppendingPathComponent:@"FoodFamilyBase.sqlite"];
    NSLog(@"sqlitePath = %@", sqlitePath);
    //打开数据库函数
    //将数据库对象与数据库文件关联,并打开数据库
    int result = sqlite3_open(sqlitePath.UTF8String, &db);
    if (result == SQLITE_OK) {
        
        NSLog(@"打开数据库成功");
        
    } else {
        NSLog(@"打开失败");
    }
    
    
}

+(void)operateTable:(NSString *)sql {
    [[self class] openDB];
    int result = sqlite3_exec(db, sql.UTF8String, NULL, NULL, nil);
    if (result == SQLITE_OK) {
        
        NSLog(@"-----成功-----");
        
    } else {
        NSLog(@"*****失败*****");
    }
    
    
}



+(NSMutableArray *)selectAllFood {
    [[self class] openDB];
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM foodHot WHERE 1 = 1"];

    sqlite3_stmt *stmt = nil;

    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    
    NSMutableArray *array = [NSMutableArray array];
    
    if (result == SQLITE_OK) {
        
        NSLog(@"准备成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
//            number INTEGER PRIMARY KEY AUTOINCREMENT, tweet_id TEXT, user_image TEXT, picture_url TEXT, food_name TEXT, pv_count TEXT, want_it_total TEXT, comment_count TEXT, user_name TEXT, city_name TEXT, place_name TEXT, foodzine_id TEXT
            //取出表里每行中的每一列字段的数据
            const unsigned char *tweet_id = sqlite3_column_text(stmt, 1);
            const unsigned char *user_image = sqlite3_column_text(stmt, 2);
            const unsigned char *picture_url = sqlite3_column_text(stmt, 3);
            const unsigned char *food_name = sqlite3_column_text(stmt, 4);
            const unsigned char *pv_count = sqlite3_column_text(stmt, 5);
            const unsigned char *want_it_total = sqlite3_column_text(stmt, 6);
            const unsigned char *comment_count = sqlite3_column_text(stmt, 7);
            const unsigned char *user_name = sqlite3_column_text(stmt, 8);
            const unsigned char *city_name = sqlite3_column_text(stmt, 9);
            const unsigned char *place_name = sqlite3_column_text(stmt, 10);
            const unsigned char *foodzine_id = sqlite3_column_text(stmt, 11);
            FoodAboutModle *food = [[FoodAboutModle alloc] init];
            food.tweet_id = [NSString stringWithUTF8String:(const char*)tweet_id];
            food.user_image = [NSString stringWithUTF8String:(const char*)user_image];
            food.picture_url = [NSString stringWithUTF8String:(const char*)picture_url];
            food.food_name = [NSString stringWithUTF8String:(const char*)food_name];
            food.pv_count = [NSString stringWithUTF8String:(const char*)pv_count];
            food.want_it_total = [NSString stringWithUTF8String:(const char*)want_it_total];
            food.comment_count = [NSString stringWithUTF8String:(const char*)comment_count];
            food.user_name = [NSString stringWithUTF8String:(const char*)user_name];
            food.city_name = [NSString stringWithUTF8String:(const char*)city_name];
            food.place_name = [NSString stringWithUTF8String:(const char*)place_name];
            food.foodzine_id = [NSString stringWithUTF8String:(const char*)foodzine_id];
            [array addObject:food];
            
        }

        return array;
  
    } else {
        NSLog(@"查询失败");
        return nil;
    }
    
    
    
}
+(NSMutableArray *)selectAllFoodMen {
    [[self class] openDB];
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM foodMen WHERE 1 = 1"];
    
    sqlite3_stmt *stmt = nil;
    
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    
    NSMutableArray *array = [NSMutableArray array];
    
    if (result == SQLITE_OK) {
        
        NSLog(@"准备成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
//user_image TEXT, user_id TEXT, name TEXT, level_title TEXT, reputation TEXT, tobeloved TEXT, foodzine_id TEXT
            //取出表里每行中的每一列字段的数据
            const unsigned char *user_image = sqlite3_column_text(stmt, 1);
            const unsigned char *user_id = sqlite3_column_text(stmt, 2);
            const unsigned char *name = sqlite3_column_text(stmt, 3);
            const unsigned char *level_title = sqlite3_column_text(stmt, 4);
            const unsigned char *reputation = sqlite3_column_text(stmt, 5);
            const unsigned char *tobeloved = sqlite3_column_text(stmt, 6);
            const unsigned char *foodzine_id = sqlite3_column_text(stmt, 7);
   
            FoodMenModle *men = [[FoodMenModle alloc] init];
            men.user_image = [NSString stringWithUTF8String:(const char*)user_image];
            men.user_id = [NSString stringWithUTF8String:(const char*)user_id];
            men.name = [NSString stringWithUTF8String:(const char*)name];
            men.level_title = [NSString stringWithUTF8String:(const char*)level_title];
            men.level_title = [NSString stringWithUTF8String:(const char*)level_title];
            men.reputation = [NSString stringWithUTF8String:(const char*)reputation];
            men.tobeloved = [NSString stringWithUTF8String:(const char*)tobeloved];
            men.foodzine_id = [NSString stringWithUTF8String:(const char*)foodzine_id];

            [array addObject:men];
            
        }
        
        return array;
        
    } else {
        NSLog(@"查询失败");
        return nil;
    }
    
    
    
}
+(NSMutableArray *)selectAllFoodMen1 {
    [[self class] openDB];
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM foodMen1 WHERE 1 = 1"];
    
    sqlite3_stmt *stmt = nil;
    
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    
    NSMutableArray *array = [NSMutableArray array];
    
    if (result == SQLITE_OK) {
        
        NSLog(@"准备成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            //user_image TEXT, user_id TEXT, name TEXT, level_title TEXT, reputation TEXT, tobeloved TEXT, foodzine_id TEXT
            //取出表里每行中的每一列字段的数据
            const unsigned char *user_image = sqlite3_column_text(stmt, 1);
            const unsigned char *user_id = sqlite3_column_text(stmt, 2);
            const unsigned char *name = sqlite3_column_text(stmt, 3);
            const unsigned char *level_title = sqlite3_column_text(stmt, 4);
            const unsigned char *reputation = sqlite3_column_text(stmt, 5);
            const unsigned char *tobeloved = sqlite3_column_text(stmt, 6);
            const unsigned char *foodzine_id = sqlite3_column_text(stmt, 7);
            
            FoodMenModle *men = [[FoodMenModle alloc] init];
            men.user_image = [NSString stringWithUTF8String:(const char*)user_image];
            men.user_id = [NSString stringWithUTF8String:(const char*)user_id];
            men.name = [NSString stringWithUTF8String:(const char*)name];
            men.level_title = [NSString stringWithUTF8String:(const char*)level_title];
            men.level_title = [NSString stringWithUTF8String:(const char*)level_title];
            men.reputation = [NSString stringWithUTF8String:(const char*)reputation];
            men.tobeloved = [NSString stringWithUTF8String:(const char*)tobeloved];
            men.foodzine_id = [NSString stringWithUTF8String:(const char*)foodzine_id];
            
            [array addObject:men];
            
        }
        
        return array;
        
    } else {
        NSLog(@"查询失败");
        return nil;
    }
    
    
    
}
+(NSMutableArray *)selectAllFoodMagazine {
    [[self class] openDB];
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM foodMagazine WHERE 1 = 1"];
    
    sqlite3_stmt *stmt = nil;
    
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    
    NSMutableArray *array = [NSMutableArray array];
    
    if (result == SQLITE_OK) {
        
        NSLog(@"准备成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
//picture_url, serial_number, title, introduction, user_image, user_name, foodzine_id, tweet_id
            //取出表里每行中的每一列字段的数据
            const unsigned char *picture_url = sqlite3_column_text(stmt, 1);
            const unsigned char *serial_number = sqlite3_column_text(stmt, 2);
            const unsigned char *title = sqlite3_column_text(stmt, 3);
            const unsigned char *introduction = sqlite3_column_text(stmt, 4);
            const unsigned char *user_image = sqlite3_column_text(stmt, 5);
            const unsigned char *user_name = sqlite3_column_text(stmt, 6);
            const unsigned char *foodzine_id = sqlite3_column_text(stmt, 7);
            const unsigned char *tweet_id = sqlite3_column_text(stmt, 8);
            FoodMagazineModle *magazine = [[FoodMagazineModle alloc] init];
            magazine.picture_url = [NSString stringWithUTF8String:(const char*)picture_url];
            magazine.serial_number = [NSString stringWithUTF8String:(const char*)serial_number];
            magazine.title = [NSString stringWithUTF8String:(const char*)title];
            magazine.introduction = [NSString stringWithUTF8String:(const char*)introduction];
            magazine.user_image = [NSString stringWithUTF8String:(const char*)user_image];
            magazine.user_name = [NSString stringWithUTF8String:(const char*)user_name];
            magazine.foodzine_id = [NSString stringWithUTF8String:(const char*)foodzine_id];
            magazine.tweet_id = [NSString stringWithUTF8String:(const char*)tweet_id];

            [array addObject:magazine];
            
        }
        
        return array;
        
    } else {
        NSLog(@"查询失败");
        return nil;
    }
    
    
    
}

+(MapCityModle *)selectMapCity:(NSString *)cityPinyinName {
    
    [[self class] openDB];
    
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM mapCity WHERE cityChineseName = '%@'", cityPinyinName];

    sqlite3_stmt *stmt = nil;
    
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    
    if (result == SQLITE_OK) {
        
        MapCityModle *city = [[MapCityModle alloc] init];
        NSLog(@"准备成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
//cityId TEXT, cityChineseName TEXT, cityPinyinName TEXT
            const unsigned char *cityId = sqlite3_column_text(stmt, 1);
            const unsigned char *cityChineseName = sqlite3_column_text(stmt, 2);

            
            city.cityId = [NSString stringWithUTF8String:(const char*)cityId];
            city.cityChineseName = [NSString stringWithUTF8String:(const char*)cityChineseName];

        }
        
        return city;
        
        
    } else {
        NSLog(@"查询失败");
        return nil;
    }

    
    
    
}


+(NSMutableArray *)selectMapCityAll {
    
    [[self class] openDB];
    
    NSString *sql = [NSString stringWithFormat:@"SELECT * FROM mapCity WHERE 1 = 1"];
    
    sqlite3_stmt *stmt = nil;
    
    int result = sqlite3_prepare_v2(db, sql.UTF8String, -1, &stmt, nil);
    
    NSMutableArray *array = [NSMutableArray array];
    if (result == SQLITE_OK) {
        NSLog(@"准备成功");
        while (sqlite3_step(stmt) == SQLITE_ROW) {
            MapCityModle *city = [[MapCityModle alloc] init];
            //cityId TEXT, cityChineseName TEXT, cityPinyinName TEXT
            const unsigned char *cityId = sqlite3_column_text(stmt, 1);
            const unsigned char *cityChineseName = sqlite3_column_text(stmt, 2);

            
            city.cityId = [NSString stringWithUTF8String:(const char*)cityId];
            city.cityChineseName = [NSString stringWithUTF8String:(const char*)cityChineseName];

            [array addObject:city];
        }

        return array;
        
        
    } else {
        NSLog(@"查询失败");
        return nil;
    }
    
    
    
    
}

@end
